Spring JPA dynamic query example

2023-04-14 01:43| 来源: 网络整理| 查看: 265

Table of Contents

1. OverviewSteps to Generate Dynamic Query In Spring JPA:2. Spring JPA dynamic query examples2.1 JPA Dynamic Criteria with equal2.2 JPA dynamic with equal and like2.3 JPA dynamic like for multiple fields2.4 JPA dynamic Like and between criteria2.5 JPA dynamic query with Paging or Pagination2.6 JPA Dynamic Order3. Conclusion4. References5. Source CodeWas this post helpful?1. Overview

In this article, We will learn Spring JPA dynamic query example. As we all know that we can write Spring JPA query and fetch the data as like:

@Query("SELECT em FROM Employee em WHERE em.employeeId = :employeeId") List getEmployeeById(@Param("employeeId") Long employeeId);

But while applying filters, It may require to skips some parameters and based on that fetching the database results, In those cases, we require to generate a dynamic query based on parameters.

In the above query, Let assume that our requirement is like if passed employeeId then fetch only that single employee or If pass a NULL value in an employeeId if then it will fetch all the employee but this scenario simple query (as above) will not work in that case we need to generate a dynamic query.

Steps to Generate Dynamic Query In Spring JPA:Extends JpaSpecificationExecutor in Repository as like:import com.javadeveloperzone.model.Employee; import org.springframework.data.jpa.repository.JpaSpecificationExecutor; import org.springframework.data.repository.CrudRepository; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; @Repository @Transactional public interface EmployeeDAO extends CrudRepository,JpaSpecificationExecutor{ }

2. Write Specification for the query:

Here we have created List of Predicate, We can add criteria to predicates based on our requirements and using those predicates criteria spring JPA will generate a dynamic query. Here is employeeName will be null then we haven’t added Predicate so it will fetch all the Employees otherwise match to specific names.

public List findByCriteria(String employeeName){ return employeeDAO.findAll(new Specification() { @Override public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder criteriaBuilder) { List predicates = new ArrayList(); if(employeeName!=null) { predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("employeeName"), employeeName))); } return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }); }2. Spring JPA dynamic query examples2.1 JPA Dynamic Criteria with equalpublic List findByCriteria(String employeeName){ return employeeDAO.findAll(new Specification() { @Override public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder criteriaBuilder) { List predicates = new ArrayList(); if(employeeName!=null) { predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("employeeName"), employeeName))); } return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }); }

employeeService.findByCriteria("Harry"); // Fetch only those whose name is Harry

SQL Query: select * from employee where employeeName=?

employeeService.findByCriteria(null); // Fetch all employees

SQL Query: select * from employee

2.2 JPA dynamic with equal and likepublic List findByCriteria(String employeeName,String employeeRole){ return employeeDAO.findAll(new Specification() { @Override public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder criteriaBuilder) { List predicates = new ArrayList(); if(employeeName!=null) { predicates.add(criteriaBuilder.and(criteriaBuilder.like(root.get("employeeName"), "%"+employeeName+"%"))); } if(employeeRole!=null){ predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("employeeRole"), employeeRole))); } return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }); }

employeeService.findByCriteria("Jo","ADMIN"); // Fetch all names like *Jo* and role is ADMIN

SQL Query: select * from employee where (employeeName like ?) and employeeRole=?

employeeService.findByCriteria(null,"ADMIN"); // Fetch employee whose role is ADMIN

SQL Query: select * from employee where employeeRole=?

employeeService.findByCriteria(null,null); // Fetch all employees

SQL Query: select * from employee

2.3 JPA dynamic like for multiple fieldspublic List findByLikeCriteria(String text){ return employeeDAO.findAll(new Specification() { @Override public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder criteriaBuilder) { List predicates = new ArrayList(); if(text!=null) { predicates.add(criteriaBuilder.or( criteriaBuilder.like(root.get("employeeName"), "%" + text + "%"), criteriaBuilder.like(root.get("employeeEmail"), "%" + text + "%")) ); } return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }); }

employeeService.findByLikeCriteria("info"); // Fetch all employees whole email or name contains “info”

SQL Query: select * from employee where employeeName like ? or employeeEmail like ?

employeeService.findByLikeCriteria(null); // Fetch all employees

SQL Query: select * from employee

2.4 JPA dynamic Like and between criteriapublic List findByLikeAndBetweenCriteria(String text,int employeeIdStart, int employeeIdEnd){ return employeeDAO.findAll(new Specification() { @Override public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder criteriaBuilder) { List predicates = new ArrayList(); if(text!=null) { predicates.add(criteriaBuilder.or(criteriaBuilder.like(root.get("employeeName"), "%" + text + "%"), criteriaBuilder.like(root.get("employeeEmail"), "%" + text + "%"))); } if(employeeIdStart!=0 && employeeIdEnd!=0){ predicates.add(criteriaBuilder.between(root.get("employeeId"),employeeIdStart,employeeIdEnd)); } return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }); }

List findByLikeAndBetweenCriteria(String employeeName, int employeeIdStart, int employeeIdEnd);

SQL Query: select * from employee where (employeeName like ? or employeeEmail like ?) and (employeeId between 15 and 20)

employeeService.findByLikeAndBetweenCriteria("info",0,0); // Fetch all employees whole email or name contains “info”

SQL Query: select * from employee where employeeName like ? or employeeEmail like ?

employeeService.findByLikeAndBetweenCriteria(null,0,0); // Fetch all employees

SQL Query: select * from employee

2.5 JPA dynamic query with Paging or Paginationpublic List findByPagingCriteria(String employeeName,Pageable pageable){ Page page = employeeDAO.findAll(new Specification() { @Override public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder criteriaBuilder) { List predicates = new ArrayList(); if(employeeName!=null) { predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("employeeName"), employeeName))); } return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }, pageable); page.getTotalElements(); // get total elements page.getTotalPages(); // get total pages return page.getContent(); // get List of Employee }

employeeService.findByPagingCriteria("Jone", new PageRequest(0,10)); In pageRequest, First Param is page number, Second for page size

SQL Query: select * from employee where employeeName=? limit ?

2.6 JPA Dynamic Orderpublic List findByCriteria(String employeeName,String fieldName){ return employeeRepository.findAll(new Specification() { @Override public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder criteriaBuilder) { List predicates = new ArrayList(); if(employeeName!=null) { predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("employeeName"), employeeName))); } query.orderBy(criteriaBuilder.desc(root.get(fieldName))); // for desc // query.orderBy(criteriaBuilder.asc(root.get(fieldName))); // for asc return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }); }

employeeService.findByCriteria("Harry","joinedDate");

SQL Query: select * from employee where employeeName=? order by joinedDate desc

3. Conclusion

We learned that how we can generate dynamic query or query based on parameters in spring JPA. We have also attached source code with spring boot + JPA Dyanimc query.

4. ReferencesSpring JPA Specifications documentSpring JPA documentation5. Source Code

spring-boot-jpa-dynamic-query-example (54 KB)

Was this post helpful? Let us know if you liked the post. That’s the only way we can improve.

Share via:

Facebook Twitter LinkedIn More

【本文地址】

公司简介

联系我们